package com.luxsoft.siipap.em.replica.importadores.cxc.parches;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Types;
import java.text.MessageFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.luxsoft.siipap.model.Periodo;
import com.luxsoft.siipap.service.ServiceLocator2;
import com.luxsoft.siipap.util.DateUtil;

public class ValidarImportesDeCargosAbonos {
	
	
	
	public void validarAbonosPagos(final Periodo p){
		//Ids de Oracle
		String sql="select FECHA,SUM(IMPORTE) AS IMPORTE,SUM(DOCTO) from V_APLIC_ORACLE where fecha between ? and  ? AND TIPO_ID=\'PAGO\' group by FECHA order by FECHA asc";
		SqlParameterValue p1=new SqlParameterValue(Types.DATE,p.getFechaInicial());
		SqlParameterValue p2=new SqlParameterValue(Types.DATE,p.getFechaFinal());
		
		final List<Map<String, Object>> rows=ServiceLocator2.getAnalisisJdbcTemplate().queryForList(sql, new Object[]{p1,p2});
		
		ServiceLocator2.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				int buff=0;
				for(Map<String, Object> row:rows){
					Date dia=(Date)row.get("FECHA");
					BigDecimal impOracle=(BigDecimal)row.get("IMPORTE");
					BigDecimal imp=(BigDecimal)session.createQuery("select sum(a.importe) from AplicacionDePago a where a.fecha=?")
					.setParameter(0, dia,Hibernate.DATE)
					.setMaxResults(1)
					.uniqueResult();
					String pattern="  {0}\t{1}\t{2}\t{3}";
					
					System.out.println(MessageFormat.format(pattern, DateUtil.getDate(dia),impOracle,imp,impOracle.add(imp)));
					if(buff++%20==0){						
						session.clear();
						session.flush();
					}
				}
				
				return null;
			}
			
		});
				
		
	}
	
	public void validarAbonosNotas(final Periodo p){
		//Ids de Oracle
		String sql="select FECHA,SUM(IMPORTE) AS IMPORTE,SUM(DOCTO) from V_APLIC_ORACLE where fecha between ? and  ? AND TIPO_ID=\'NOTA\' group by FECHA order by FECHA asc";
		SqlParameterValue p1=new SqlParameterValue(Types.DATE,p.getFechaInicial());
		SqlParameterValue p2=new SqlParameterValue(Types.DATE,p.getFechaFinal());
		
		final List<Map<String, Object>> rows=ServiceLocator2.getAnalisisJdbcTemplate().queryForList(sql, new Object[]{p1,p2});
		
		ServiceLocator2.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				int buff=0;
				for(Map<String, Object> row:rows){
					Date dia=(Date)row.get("FECHA");
					BigDecimal impOracle=(BigDecimal)row.get("IMPORTE");
					BigDecimal imp=(BigDecimal)session.createQuery("select sum(a.importe) from AplicacionDeNota a where a.fecha=? and a.abono.origen=\'CRE\'")
					.setParameter(0, dia,Hibernate.DATE)
					.setMaxResults(1)
					.uniqueResult();
					String pattern="  {0}\t{1}\t{2}\t{3}";
					
					System.out.println(MessageFormat.format(pattern, DateUtil.getDate(dia),impOracle,imp,impOracle.add(imp)));
					if(buff++%20==0){						
						session.clear();
						session.flush();
					}
				}
				
				return null;
			}
			
		});
				
		
	}
	
	
	
	
	public static void main(String[] args) {
		ValidarImportesDeCargosAbonos b=new ValidarImportesDeCargosAbonos();
		Periodo per=new Periodo("01/01/2008","28/02/2009");
		List<Periodo> meses=Periodo.periodosMensuales(per);
		for(Periodo mes:meses){
			System.out.println("Mes: "+mes);			
			//b.validarAbonosPagos(mes);
			b.validarAbonosNotas(mes);
		}
		
	}

}
